package com.yunyike.app.data.controller;

import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.json.JSONException;
import org.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.yunyike.app.constants.ExcelConstants;
import com.yunyike.app.data.model.Merchandise;
import com.yunyike.app.data.service.IMerchandiseService;
import com.yunyike.app.util.Common;
import com.yunyike.app.util.PKUtils;

@Controller
@RequestMapping(value = "/merchandise")
public class MerchandiseExcelController {

	private Logger LOGGER = LoggerFactory.getLogger(MerchandiseExcelController.class);

	
	@Autowired
	private IMerchandiseService merchandiseService;
	
	@RequestMapping("/list")
	public String getList(HttpServletRequest request, HttpServletResponse response, Model model,
			Merchandise mer) {
		return "/excel/merchandise_excel";
	}
	
	@RequestMapping("/exportexcel")
	public String exportExcel(HttpServletRequest request, HttpServletResponse response, 
			Model model,Merchandise mer) {
		try {
			String workName = "商品";
			List<Merchandise> merchandiseList = merchandiseService.findMerchandises();
			if (merchandiseList != null && merchandiseList.size() > 0) {
				// 导出表格
				this.downLoadExcel(merchandiseList, workName, request, response);
			}
		} catch (Exception e) {
			LOGGER.error("下载表格异常:" + e.getMessage(), e);
			model.addAttribute("message", "下载表格异常");
			return "error";
		}
		model.addAttribute("message", "success");
		return "/excel/merchandise_excel";
	}

	private void downLoadExcel(List<Merchandise> list, String workName, HttpServletRequest request,
			HttpServletResponse response) throws Exception {

		try {
			String[] headTitle = { "merchandise_id","merchandise_number","merchandise_name_CN","merchandise_name_EN","merchandise_desc",
									"merchandise_spec","merchandise_weight","merchandise_volume","merchandise_price","merchandise_currency",
									"merchandise_icon","merchandise_barcode","merchandise_qrcode","merchandise_domestic_price","shop_id",
									"ctime"};
			Map<String, Object> workMap = Common.geneBookBegin(workName, headTitle);
			HSSFWorkbook workbook = (HSSFWorkbook) workMap.get("workbook");
			HSSFSheet sheet = (HSSFSheet) workMap.get("sheet");
			HSSFRow row = (HSSFRow) workMap.get("row");
			for (int i = 0; i < list.size(); i++) {
				Merchandise exportDTO = list.get(i);
				HSSFCellStyle styleRow = workbook.createCellStyle();
				styleRow.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
				row = sheet.createRow(i + 1);
				Common.geneCell(row, ExcelConstants.SHORT0, exportDTO.getMerchandiseId(), styleRow);
				Common.geneCell(row, ExcelConstants.SHORT1, exportDTO.getMerchandiseNumber(), styleRow);
				Common.geneCell(row, ExcelConstants.SHORT2, exportDTO.getMerchandiseNameCn(), styleRow);
				Common.geneCell(row, ExcelConstants.SHORT3, exportDTO.getMerchandiseNameEn(), styleRow);
				Common.geneCell(row, ExcelConstants.SHORT4, exportDTO.getMerchandiseDesc(), styleRow);
				Common.geneCell(row, ExcelConstants.SHORT5, exportDTO.getMerchandiseSpec(), styleRow);
				Common.geneCell(row, ExcelConstants.SHORT6, exportDTO.getMerchandiseWeight()+"", styleRow);
				Common.geneCell(row, ExcelConstants.SHORT7, exportDTO.getMerchandiseVolume()==null?"":exportDTO.getMerchandiseVolume().toString(), styleRow);
				Common.geneCell(row, ExcelConstants.SHORT8, exportDTO.getMerchandisePrice()==null?"":exportDTO.getMerchandisePrice().toString(), styleRow);
				Common.geneCell(row, ExcelConstants.SHORT9, exportDTO.getMerchandiseCurrency(), styleRow);
				Common.geneCell(row, ExcelConstants.SHORT10, exportDTO.getMerchandiseIcon(), styleRow);
				Common.geneCell(row, ExcelConstants.SHORT11, exportDTO.getMerchandiseBarcode(), styleRow);
				Common.geneCell(row, ExcelConstants.SHORT12, exportDTO.getMerchandiseQrcode(), styleRow);
				Common.geneCell(row, ExcelConstants.SHORT13, exportDTO.getMerchandiseDomesticPrice()==null?"":""+exportDTO.getMerchandiseDomesticPrice(), styleRow);
				Common.geneCell(row, ExcelConstants.SHORT14, exportDTO.getShopId(), styleRow);
				Common.geneCell(row, ExcelConstants.SHORT15, exportDTO.getCtime()==null?"":Common.dateToString(exportDTO.getCtime()), styleRow);
			
			}
			LOGGER.info("success!");
			Common.writeBookEnd(workName, response, workbook);
		} catch (Exception e) {
			LOGGER.error("error::" + e.getMessage());
			throw new Exception(e);
		}
	}
	
	@RequestMapping("/importexcel")
	public String importExcel(@RequestParam("excelFile")MultipartFile excelFile,Model model) {
		try {
			LOGGER.info("importexcel--begin----");
			InputStream inputStream = excelFile.getInputStream();  
			Map result  = this.readCheckExcel(inputStream);
			List<String> errorList= (List<String>)result.get("errorMsg");
			if(errorList!=null&&errorList.size()>0){
				model.addAttribute("errorList", errorList);
			}else{
				List<Merchandise> list = (List<Merchandise>)result.get("Merchandises");
				int rows = (int)result.get("rows");
				LOGGER.info("importexcel--check-success："+rows+"条");
				Map saveMap = this.saveMerchandises(list);
				String errorMsg = (String)saveMap.get("errorMsg");
				int successRows = (int)saveMap.get("successRows");
				LOGGER.info("importexcel--save-success："+successRows+"条");
				model.addAttribute("errorMsg", errorMsg);
				model.addAttribute("message", "成功获取数据"+rows+"条，成功保存入库"+successRows+"条");
			}
			LOGGER.info("importexcel--end----");
		} catch (Exception e) {
			e.printStackTrace();
			LOGGER.error("下载表格异常:" + e.getMessage(), e);
			model.addAttribute("message", "下载表格异常");
		}
		return "/excel/merchandise_excel";
	}
	
	private Map saveMerchandises(List<Merchandise> merList){
		Map map = new HashMap();
		StringBuffer errorMsg = new StringBuffer("");
		int successRows = 0;
		
		for(Merchandise mer:merList){
			if(mer.getMerchandiseId()==null||"".equals(mer.getMerchandiseId())){
				mer.setMerchandiseId(UUID.randomUUID().toString());
				mer.setMerchandiseNumber(PKUtils.getPrimaryKey());
				int result =merchandiseService.addMerchandise(mer);
				if(result<1){
					errorMsg.append("[merchandise_name_CN]="+mer.getMerchandiseNameCn()+"==>添加失败</br>");
					continue;
				}
			}else{
//				Merchandise merchan = merchandiseService.findMerchandiseById(mer.getMerchandiseId());
				if(mer.getMerchandiseNumber()==null||"".equals(mer.getMerchandiseNumber()))
					mer.setMerchandiseNumber(PKUtils.getPrimaryKey());
				int result = merchandiseService.updateMerchandise(mer);
				if(result<1){
					errorMsg.append("[merchandise_id]="+mer.getMerchandiseId()+"==>更新失败</br>");
					continue;
				}
			}
			successRows++;
		}
		map.put("errorMsg", errorMsg.toString());
		map.put("successRows", successRows);
		return map;
	}
	
	
	private Map readCheckExcel(InputStream is) throws Exception {
		Map result = new HashMap();
		
		List<Merchandise> list = new ArrayList<Merchandise>();
		List<String> errorMsg = new ArrayList<String>();
		LOGGER.info("read check Excel--begin----");
		int rows = 0;// excel处理总行数
		Map merIdUniqe = new HashMap();
		Map merNumUniqe = new HashMap();

		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
		// 循环工作表Sheet
		// for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets();
		// numSheet++) {
		HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
		// 循环行Row
		
		String tempStr = "";
		for (int rowNum = ExcelConstants.EXCEL_FIRST_ROW; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
			StringBuffer errorStr = new StringBuffer("");
			Merchandise mer = null;
			HSSFRow hssfRow = hssfSheet.getRow(rowNum);
			if (hssfRow == null) {
				continue;
			}
			String mercId = Common.getValue(hssfRow, 0);
			String mercNum = Common.getValue(hssfRow, 1);
			if(mercNum.contains(".")){
				errorStr.append("[merchandise_number]改列单元格格式必须为文本格式</br>");
			}
			String mercName = Common.getValue(hssfRow, 2);
			String mercNameEn = Common.getValue(hssfRow, 3);
			String mercDesc = Common.getValue(hssfRow, 4);
			String mercSpec = Common.getValue(hssfRow, 5);
			String mercWeight = Common.getValue(hssfRow, 6);
			String mercVolumn = Common.getValue(hssfRow, 7);
			String mercPrice = Common.getValue(hssfRow, 8);
			String mercCur = Common.getValue(hssfRow, 9);
			String mercIcon = Common.getValue(hssfRow, 10);
			String mercBarcode = Common.getValue(hssfRow, 11);
			if(mercBarcode.contains(".")){
				errorStr.append("[merchandise_barcode]改列单元格格式必须为文本格式</br>");
			}
			String qrCode = Common.getValue(hssfRow, 12);
			String domesticPrice = Common.getValue(hssfRow, 13);
			String shopId = Common.getValue(hssfRow, 14);
			String cTime = Common.getValue(hssfRow, 15);
			if ("".equals(cTime))
				cTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());

			if ("".equals(mercId) && "".equals(mercName) && "".equals(mercPrice)) {
				break;
			}

			mer = new Merchandise();
			if (!"".equals(mercId)) {
				if (merIdUniqe.containsKey(mercId)) {
					errorStr.append("A列:" + mercId + "主键重复</br>");
				} else {
					merIdUniqe.put(mercId, mercId + mercNum);
				}
				mer.setMerchandiseId(mercId);
			}
			if (!"".equals(mercNum)) {
				if (merNumUniqe.containsKey(mercNum)) {
					errorStr.append("B列:" + mercNum + "商品编号重复</br>");
				} else {
					merNumUniqe.put(mercNum, mercId + mercNum);
				}
				mer.setMerchandiseNumber(mercNum);
			}
			try {
				tempStr = Common.validate(mercId, String.class, 36, false);
				if (!"".equals(tempStr)) {
					errorStr.append("A列:" + tempStr + "</br>");
				}
				tempStr = Common.validate(mercNum, String.class, 45, false);
				if (!"".equals(tempStr)) {
					errorStr.append("B列:" + tempStr + "</br>");
				}
				tempStr = Common.validate(mercName, String.class, 50, false);
				if (!"".equals(tempStr)) {
					errorStr.append("C列:" + tempStr + "</br>");
				}
				tempStr = Common.validate(mercNameEn, String.class, 100, false);
				if (!"".equals(tempStr)) {
					errorStr.append("D列:" + tempStr + "</br>");
				}
				tempStr = Common.validate(mercDesc, String.class, 1024, false);
				if (!"".equals(tempStr)) {
					errorStr.append("E列:" + tempStr + "</br>");
				}
				tempStr = Common.validate(mercSpec, String.class, 50, false);
				if (!"".equals(tempStr)) {
					errorStr.append("F列:" + tempStr + "</br>");
				}
				if (!"".equals(mercWeight)) {
					tempStr = Common.validate(Double.parseDouble(mercWeight), Double.class, null, false);
					if (!"".equals(tempStr)) {
						errorStr.append("G列:" + tempStr + "</br>");
					}
					mer.setMerchandiseWeight(Double.parseDouble(mercWeight));
				}
				if (!"".equals(mercVolumn)) {
					tempStr = Common.validate(Double.parseDouble(mercVolumn), Double.class, null, false);
					if (!"".equals(tempStr)) {
						errorStr.append("H列:" + tempStr + "</br>");
					}
					mer.setMerchandiseVolume(Double.parseDouble(mercVolumn));
				}
				if (!"".equals(mercPrice)) {
					tempStr = Common.validate(Double.parseDouble(mercPrice), Double.class, null, false);
					if (!"".equals(tempStr)) {
						errorStr.append("I列:" + tempStr + "</br>");
					}
					mer.setMerchandisePrice(Double.parseDouble(mercPrice));
				}
				tempStr = Common.validate(mercCur, String.class, 10, false);
				if (!"".equals(tempStr)) {
					errorStr.append("J列:" + tempStr + "</br>");
				}
				tempStr = Common.validate(mercIcon, String.class, 200, false);
				if (!"".equals(tempStr)) {
					errorStr.append("K列:" + tempStr + "</br>");
				}
				tempStr = Common.validate(mercBarcode, String.class, 50, false);
				if (!"".equals(tempStr)) {
					errorStr.append("L列:" + tempStr + "</br>");
				}
				tempStr = Common.validate(qrCode, String.class, 1024, false);
				if (!"".equals(tempStr)) {
					errorStr.append("M列:" + tempStr + "</br>");
				}
				if (!"".equals(domesticPrice)) {
					tempStr = Common.validate(Double.parseDouble(domesticPrice), Double.class, null, false);
					if (!"".equals(tempStr)) {
						errorStr.append("N列:" + tempStr + "</br>");
					}
					mer.setMerchandiseDomesticPrice(Double.parseDouble(domesticPrice));
				}
				tempStr = Common.validate(shopId, String.class, 36, false);
				if (!"".equals(tempStr)) {
					errorStr.append("O列:" + tempStr + "</br>");
				}
				tempStr = Common.validate(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(cTime), Date.class, null,
						false);
				if (!"".equals(tempStr)) {
					errorStr.append("P列:" + tempStr + "</br>");
				}
			} catch (NumberFormatException e) {
				e.printStackTrace();
				errorStr.append("检查数字和时间相关字段，存在格式化错误的值</br>");
			}
			mer.setShopId(shopId);
			mer.setMerchandiseSpec(mercSpec);
			mer.setMerchandiseQrcode(qrCode);
			mer.setMerchandiseIcon(mercIcon);
			mer.setMerchandiseNameCn(mercName);
			mer.setMerchandiseNameEn(mercNameEn);
			mer.setMerchandiseDesc(mercDesc);
			mer.setMerchandiseBarcode(mercBarcode);
			mer.setCtime(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(cTime));
			mer.setMerchandiseCurrency(mercCur);

			if (!"".equals(errorStr.toString())) {
				errorMsg.add("第" + rowNum + "行:" + errorStr.toString());
				continue;
			}
			rows++;
			list.add(mer);
		}
		
		// }
		result.put("errorMsg", errorMsg);
		result.put("Merchandises", list);
		result.put("rows", rows);
		LOGGER.info("read check Excel--end----rows="+rows);
		
		return result;
	}
 
  
	
	
}
